/**
 * 
 */
package com.lt.flashcard.model.service;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import com.lt.flashcard.Utility;
import com.lt.flashcard.model.entity.ActionResult;
import com.lt.flashcard.model.entity.Flashcard;
import com.lt.flashcard.model.entity.FlashcardSet;
import com.lt.flashcard.model.entity.Partner;
import com.lt.flashcard.model.entity.ProductCode;
import com.lt.flashcard.model.entity.Productcategory;
import com.lt.flashcard.model.entity.Promotion;
import com.lt.flashcard.model.entity.User;
import com.mongodb.gridfs.GridFSInputFile;

/**
 * @author Administrator
 *
 */
public class ImportDataService {

    private static final Logger log = Logger.getLogger(ImportDataService.class);

    /**
     * The working folder
     */
    private String importFolder = "";

    public static final String IMPORT_FLASHCARD = "flashcard";
    public static final String IMPORT_FLASHCARDSET = "flashcardset";
    public static final String IMPORT_PRODUCTCODE = "productcode";
    public static final String FLASHCARDS_FILES_PATH = Utility.getWebAppPath()
            + Utility.getConfiguration("com.lt.flashcard.flashcardFilesToImportFolder", "files_to_import\\flashcards\\");
    public static final String FLASHCARDS_IMAGES_PATH = Utility.getWebAppPath()
            + Utility.getConfiguration("com.lt.flashcard.flashcardImagesToImportFolder", "files_to_import\\flashcards\\images");
    public static final String FLASHCARDSETS_FILES_PATH = Utility.getWebAppPath()
            + Utility.getConfiguration("com.lt.flashcard.flashcardSetFilesToImportFolder", "files_to_import\\flashcardsets\\");
    public static final String FLASHCARDSETS_IMAGES_PATH = Utility.getWebAppPath()
            + Utility.getConfiguration("com.lt.flashcard.flashcardSetImagesToImportFolder", "files_to_import\\flashcardsets\\images");
    public static final String PRODUCTCODE_FILES_PATH = Utility.getWebAppPath()
            + Utility.getConfiguration("com.lt.flashcard.productcodeFilesToImportFolder", "files_to_import\\productcode\\");

    public ImportDataService(String workingfolder) {
        this.importFolder = workingfolder;
    }

    public List<List<String>> readCSVFile(String filePath) throws Exception {
        List<List<String>> data = new ArrayList<List<String>>();

        // create BufferedReader to read csv file
        BufferedReader br = new BufferedReader(new FileReader(filePath));
        String strLine = "";
        int lineNumber = 0;
        int tokenNumber = 0;

        // read comma separated file line by line
        while ((strLine = br.readLine()) != null) {
            lineNumber++;
            List<String> line = new ArrayList<String>();
            String[] arrLine = strLine.split(";", -1);
            for (int i = 0; i < arrLine.length; i++) {
                tokenNumber++;
                String tmp = arrLine[i];
                if (tmp.isEmpty()) {
                    line.add("");
                } else {
                    if (tmp.contains("\"")) {
                        tmp = tmp.replace("\"", "");
                    }
                    line.add(tmp);
                }
            }
            tokenNumber = 0;
            data.add(line);
        }

        return data;
    }

    /**
     * Read entire content of excel document
     * @param filePath The absolute path
     * @return
     * @throws Exception
     */
    public List<List<String>> readExcelFile(String filePath) throws Exception {
        List<List<String>> data = new ArrayList<List<String>>();
        FileInputStream inputWorkbook = new FileInputStream(filePath);
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(inputWorkbook);

            // Get the first sheet
            HSSFSheet sheet = workbook.getSheetAt(0);
            int lineNumber = 0;
            int tokenNumber = 0;
            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();
                Iterator<Cell> cells = row.cellIterator();
                List<String> line = new ArrayList<String>();
                lineNumber++;
                while (cells.hasNext()) {
                    tokenNumber++;
                    HSSFCell cell = (HSSFCell) cells.next();
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        line.add(cell.getRichStringCellValue().getString());
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                        line.add("");
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        line.add(new DecimalFormat("#").format(cell.getNumericCellValue()));
                    } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
                        line.add(String.valueOf(cell.getBooleanCellValue()));
                    } else {
                        line.add("");
                    }
                }
                tokenNumber = 0;
                data.add(line);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (inputWorkbook != null) {
                inputWorkbook.close();
            }
        }

        return data;
    }

    public boolean importFlashcardSetDataFromCSV(String filePath) {
        boolean imported = false;
        try {
            List<List<String>> data = readCSVFile(filePath);
            File[] fcsImageFiles = (new File(ImportDataService.FLASHCARDSETS_IMAGES_PATH)).listFiles();
            for (int i = 1; i < data.size(); i++) {
                List<String> lst = data.get(i);
                FlashcardSet fcs = new FlashcardSet();
                fcs.setName(lst.get(0));
                fcs.setDescription(lst.get(1));
                String imageName = lst.get(2);
                if (!imageName.isEmpty()) {
                    for (int j = 0; j < fcsImageFiles.length; j++) {
                        File f = fcsImageFiles[j];
                        String fileName = f.getName();
                        int extSepIndex = fileName.lastIndexOf(".");
                        fileName = fileName.substring(0, extSepIndex);
                        if (imageName.equals(fileName)) {
                            InputStream stream = new FileInputStream(f);
                            Repository repo = Repository.getInstance();
                            GridFSInputFile gif = repo.saveBinaryData(stream, f.getName());
                            fcs.setImage(gif.getId().toString());
                        }
                    }
                    // fc.setImage(lst.get(15));
                }
                DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                String d = df.format(new Date());
                Date created = df.parse(d);
                fcs.setCreated(created);
                fcs.setLanguage(Utility.checkNull(lst.get(3)));
                String userName = lst.get(4);
                if (!userName.isEmpty()) {
                    User u = Repository.getInstance().getUserService().findByUsername(userName);
                    if (u != null) {
                        fcs.setAuthor(u);
                    }
                }

                fcs.setRelateflashcardsetCache(Utility.checkNull(lst.get(5)));
                fcs.setOriginalflashcardsetCache(Utility.checkNull(lst.get(6)));
                fcs.setAllowedusersCache(Utility.checkNull(lst.get(7)));
                fcs.setDeniedusersCache(Utility.checkNull(lst.get(8)));
                fcs.setTestingresultCache(lst.get(9));

                String proCateName = lst.get(10);
                Productcategory pc = Repository.getInstance().getEcommerceService().getProductCategoryByName(proCateName);
                if (pc != null) {
                    fcs.setProductcategory(pc);
                }

                Repository.getInstance().getFlashcardSetService().save(fcs);
            }
            imported = true;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return imported;
    }

    /**
     * Import Flashcard Set data from Excel file
     * @param filePath
     * @return true if imported successfully and false if fail
     * @author Nguyen Nhat Nguyen
     */
    public boolean importFlashcardSetFromExcel(String filePath) {
        boolean imported = false;
        try {
            List<List<String>> data = readExcelFile(filePath);
            File[] fcsImageFiles = (new File(ImportDataService.FLASHCARDSETS_IMAGES_PATH)).listFiles();
            for (int i = 1; i < data.size(); i++) {
                List<String> lst = data.get(i);
                FlashcardSet fcs = new FlashcardSet();
                fcs.setName(lst.get(0));
                fcs.setDescription(lst.get(1));
                String imageName = lst.get(2);
                if (!imageName.isEmpty()) {
                    for (int j = 0; j < fcsImageFiles.length; j++) {
                        File f = fcsImageFiles[j];
                        String fileName = f.getName();
                        int extSepIndex = fileName.lastIndexOf(".");
                        fileName = fileName.substring(0, extSepIndex);
                        if (imageName.equals(fileName)) {
                            InputStream stream = new FileInputStream(f);
                            Repository repo = Repository.getInstance();
                            GridFSInputFile gif = repo.saveBinaryData(stream, f.getName());
                            fcs.setImage(gif.getId().toString());
                        }
                    }
                    // fc.setImage(lst.get(15));
                }
                DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                String d = df.format(new Date());
                Date created = df.parse(d);
                fcs.setCreated(created);
                fcs.setLanguage(Utility.checkNull(lst.get(3)));
                String userName = lst.get(4);
                if (!userName.isEmpty()) {
                    User u = Repository.getInstance().getUserService().findByUsername(userName);
                    if (u != null) {
                        fcs.setAuthor(u);
                    }
                }

                fcs.setRelateflashcardsetCache(Utility.checkNull(lst.get(5)));
                fcs.setOriginalflashcardsetCache(Utility.checkNull(lst.get(6)));
                fcs.setAllowedusersCache(Utility.checkNull(lst.get(7)));
                fcs.setDeniedusersCache(Utility.checkNull(lst.get(8)));
                fcs.setTestingresultCache(lst.get(9));

                String proCateName = lst.get(10);
                Productcategory pc = Repository.getInstance().getEcommerceService().getProductCategoryByName(proCateName);
                if (pc != null) {
                    fcs.setProductcategory(pc);
                }

                Repository.getInstance().getFlashcardSetService().save(fcs);
            }
            imported = true;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return imported;
    }

    public boolean importFlashcardDataFromCSV(String filePath, Long fcsid) {
        boolean imported = false;
        try {
            List<List<String>> data = readCSVFile(filePath);
            FlashcardSet fcs = Repository.getInstance().getFlashcardSetService().findByID(fcsid);
            File[] fcImageFiles = (new File(ImportDataService.FLASHCARDS_IMAGES_PATH)).listFiles();
            for (int i = 1; i < data.size(); i++) {
                List<String> lst = data.get(i);
                Flashcard fc = new Flashcard();
                String imageName = lst.get(14);
                if (!imageName.isEmpty()) {
                    for (int j = 0; j < fcImageFiles.length; j++) {
                        File f = fcImageFiles[j];
                        String fileName = f.getName();
                        int extSepIndex = fileName.lastIndexOf(".");
                        fileName = fileName.substring(0, extSepIndex);
                        if (imageName.equals(fileName)) {
                            InputStream stream = new FileInputStream(f);
                            Repository repo = Repository.getInstance();
                            GridFSInputFile gif = repo.saveBinaryData(stream, f.getName());
                            fc.setImage(gif.getId().toString());
                        }
                    }
                    // fc.setImage(lst.get(15));
                }
                fc.setNotes(lst.get(7).trim());
                fc.setTerm(lst.get(2).trim());
                fc.setDefinition(lst.get(6).trim());
                DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
                String d = df.format(new Date());
                Date created = df.parse(d);
                fc.setCreated(created);
                fc.setAdmin(1);
                fc.setPhonetic(Utility.checkNull(lst.get(4)).trim());
                fc.setWordtype(Utility.checkNull(lst.get(3)).trim());
                fc.setVietnamese(Utility.checkNull(lst.get(5)).trim());
                fc.setSymnonym(Utility.checkNull(lst.get(8)).trim());
                fc.setNounthing(Utility.checkNull(lst.get(10)).trim());
                fc.setNounperson(Utility.checkNull(lst.get(9)).trim());
                fc.setVerb(Utility.checkNull(lst.get(11)).trim());
                fc.setAdjective(Utility.checkNull(lst.get(12)).trim());
                fc.setAdverb(Utility.checkNull(lst.get(13)).trim());
                fc.setFlashcardSet(fcs);

                fcs.getFlashcards().add(fc);
            }
            Repository.getInstance().getFlashcardSetService().save(fcs);
            imported = true;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return imported;
    }

    /**
     * Do import to flashcard set
     * @param filePath The absolute path to the excel file
     * @param fcs The target flashcardset
     * @return
     */
    public String importFlashcardFromExcel(String filename, FlashcardSet fcs) {
        try {
            String filePath = importFolder + "/" + filename;
            List<List<String>> data = readExcelFile(filePath);
            String dirName = filename.replace(".xls", "");
            String imgdir = filePath.replace(".xls", "");
            if (log.isInfoEnabled()) {
                log.info(String.format("Import from file %s, Dirname: %s, Image: %s", filename, dirName, imgdir));
            }
            Repository repo = Repository.getInstance();

            List<String> addedimg = new ArrayList<String>(); // list of image id that added

            if (data.size() == 0) {
                return "Empty file " + filePath;
            }
            // scan for each rows
            for (int i = 1; i < data.size(); i++) {
                List<String> lst = data.get(i);
                String term = Utility.checkNull(lst.get(ImportMapping.FC_WORD)).trim();
                boolean exist = false;

                Flashcard fc = fcs.getFlashcardByTerm(term);
                if (fc == null) {
                    fc = new Flashcard();
                } else {
                    exist = true;
                }
                fc.setNotes(Utility.checkNull(lst.get(ImportMapping.FC_EXAMPLE)).trim());
                fc.setTerm(term);
                fc.setCategory(Utility.checkNull(lst.get(ImportMapping.FC_CATEGORY)).trim());
                fc.setDefinition(Utility.checkNull(lst.get(ImportMapping.FC_VIETNAMESE)).trim());
                fc.setPhonetic(Utility.checkNull(lst.get(ImportMapping.FC_PHONETIC)).trim());
                fc.setWordtype(Utility.checkNull(lst.get(ImportMapping.FC_TYPE)).trim());
                fc.setVietnamese(Utility.checkNull(lst.get(ImportMapping.FC_VIETNAMESE)).trim());
                fc.setSymnonym(Utility.checkNull(lst.get(ImportMapping.FC_SYMNONYM)).trim());
                fc.setNounthing(Utility.checkNull(lst.get(ImportMapping.FC_NT)).trim());
                fc.setNounperson(Utility.checkNull(lst.get(ImportMapping.FC_NP)).trim());
                fc.setVerb(Utility.checkNull(lst.get(ImportMapping.FC_V)).trim());
                fc.setAdjective(Utility.checkNull(lst.get(ImportMapping.FC_ADJ)).trim());
                fc.setAdverb(Utility.checkNull(lst.get(ImportMapping.FC_ADV)).trim());
                fc.setEnglish(Utility.checkNull(lst.get(ImportMapping.FC_ENGLISH)).trim());
                fc.setAdmin(1);
                fc.setWeight(i);

                // remove old image
                // repo.deleteBinaryData(fc.getImage());

                String img = imgdir + "/" + dirName + "-" + i + ".jpg";
                File imgF = new File(img);
                if (imgF.exists()) {
                    String imgid = Utility.saveImage(new FileInputStream(imgF));
                    fc.setImage(imgid);
                    if (log.isInfoEnabled()) {
                        log.info("Images exists: " + img);
                    }
                }

                if (exist) {
                    if (log.isInfoEnabled()) {
                        log.info("Update term " + fc.getTerm());
                    }
                } else {
                    if (!fcs.addFlashcard(fc)) {
                        return "Fail to add flashcard " + lst + " to flashcardset " + fcs.getName();
                    } else if (log.isInfoEnabled()) {
                        log.info("Added flashcard " + lst);
                    }
                }
            }
        } catch (Exception ex) {
            log.error(ex.getMessage());
            ex.printStackTrace();
            return ex.toString();
        }
        return "";
    }

    /**
     * Import product code for list of flashcardsets
     * @param filePath
     * @param fcses Array of flashcards id and name to add with product code
     * @param partner The partner or null
     * @param coin Promotion when purchased this code
     * @return
     */
    public ActionResult importProductCodeFromExcel(String filePath, Map<Long, String> fcses, Partner partner, int coin) {
        if (log.isInfoEnabled()) {
            log.info("Import code from: " + filePath + " to fcsid: " + fcses.toString());
        }

        ActionResult rs = new ActionResult();
        EcommerceService srv = Repository.getInstance().getEcommerceService();
        int count = 0;
        List<String> fails = new ArrayList<String>();

        // build product list
        try {
            List<List<String>> data = readExcelFile(importFolder + '/' + filePath);
            if (log.isInfoEnabled()) {
                log.info("Read file: " + filePath + " found: " + data.size() + " rows");
            }
            for (int i = 1; i < data.size(); i++) {
                List<String> lst = data.get(i);
                if (lst.size() <= 1) {
                    continue;
                }

                ProductCode code = new ProductCode();
                code.setCode(String.valueOf(lst.get(0)).trim());
                code.setPartId(String.valueOf(lst.get(1).trim()));
                code.setProducts(fcses);
                code.setPartner(partner);
                try {
                    srv.saveProductCode(code);
                    count++;

                    // add promotion for this code
                    if (coin > 0) {
                        Promotion pro = new Promotion();
                        pro.setConditiontype(Promotion.CONDITIONTYPE_ACTIVECODE);
                        pro.setTargetIdStr(code.getCode());// compare by code
                        pro.setTargetType(Promotion.TARGETTYPE_CODE);
                        pro.setType(Promotion.TYPE_ADDYELLOWCOIN);
                        pro.setValue(coin);

                        srv.savePromotion(pro);
                    }
                } catch (Exception e) {
                    fails.add(code.getCode());
                    log.error("Can't import code: " + code.getCode(), e);
                }
            }

            if (fails.size() > 0) {
                rs.addMessage(String.format("Success %s code, fails: %s", count, fails.toString()));
                log.error("Fail to import code: " + fails);
            } else {
                rs.setError(false);
                if (log.isInfoEnabled()) {
                    log.info("Import successfully " + count + " code");
                }
            }
        } catch (Exception ex) {
            log.error("Error when import code", ex);
            rs.addMessage(ex.toString());
        }
        return rs;
    }

    public String getImportFolder() {
        return importFolder;
    }

    public void setImportFolder(String importFolder) {
        this.importFolder = importFolder;
    }
}
